import { Router } from 'express'
import { ObjectId } from 'mongodb'
import _ from 'lodash'
import xlsx from 'node-xlsx'
import msg from '../msg/message.js'
import { DataBase } from '../utils/database/mongodb/database'
const fs = require('fs')

const router = Router()
let db = new DataBase()
db.initMongoDB()
db.setDatabase('gdzc')

// // 导出excel表格
// router.get('/get/excel/:unit', function (req, res, next) { // 导出所有数据
//   const unit = req.params.unit
//   let filter = {}
//   if (unit !== '全部导出') {
//     filter = { unit: unit }
//   }
//   const buffer = [{ name: 'sheet1', data: [] }]
//   db.find('items', filter, (doc) => {
//     if (!doc || doc.length === 0) {
//       res.json(msg.noFound)
//     } else {
//       buffer[0].data.push(Object.keys(doc[0]))
//       doc.forEach((item) => {
//         const tempArr = []
//         Object.keys(item).forEach((key) => {
//           tempArr.push(item[key])
//         })
//         buffer[0].data.push(tempArr)
//       })
//       const buf = xlsx.build(buffer)
//       fs.writeFile('./result.xlsx', buf, function (err) {
//         if (err) { throw err }
//         console.log('Write to xls has finished')
//         res.download('./result.xlsx')
//       })
//       // res.json(buffer)
//     }
//   })
// })

function getFormatValue (str) {
  return typeof (str) === 'undefined' ? '' : str
}

// 导出excel表格，单列
router.get('/get/excel/:unit', function (req, res, next) { // 导出所有数据
  const unit = req.params.unit
  let filter = {}
  if (unit !== '全部导出') {
    filter = { unit: unit }
  }
  const buffer = [{ name: 'sheet1', data: [] }]
  db.find('items', filter, (doc) => {
    if (!doc || doc.length === 0) {
      res.json(msg.noFound)
    } else {
      const keys = ['', 'id', '编码', '资产大类', '资产分类', '物品名称', '车牌号', '车辆用途分类',
        '执法执勤用车分类', '品牌', '财务入账时间', '制单时间', '规格型号', '价值', '单价',
        '取得日期', '会计凭证号', '资产状态', '当前位置', '编制情况', '使用单位', '使用人',
        '管理员', '备注']
      const key2 = ['id', 'code', 'firstClass', 'secondClass', 'name', 'carNumber', 'usageCarClass',
        'specialCarClass', 'brand', 'recordDate', 'touchingDate', 'model', 'price', 'unitPrice',
        'buyDate', 'voucherNumber', 'state', 'location', 'staffing', 'unit', 'user', 'admin', 'postscript']
      buffer[0].data.push(keys)
      doc.forEach((item) => {
        const line = []
        const str = '名称：' + getFormatValue(item.name) + '; 单位：' + getFormatValue(item.unit) +
          '; 领用日期：' + getFormatValue(item.receiveDate) + '; 使用人：' +
          getFormatValue(item.user) + '; 管理员：' + getFormatValue(item.admin)
        line.push(str)
        key2.forEach((key) => {
          line.push(getFormatValue(item[key]))
        })
        buffer[0].data.push(line)
      })
      const buf = xlsx.build(buffer)
      fs.writeFile('./result.xlsx', buf, function (err) {
        if (err) { throw err }
        // console.log('Write to xls has finished')
        res.download('./result.xlsx')
      })
      // res.json(buffer)
    }
  })
})

router.get('/reset', (req, res, next) => {
  db = new DataBase()
  // db.initMongoDBYndj()
  db.initMongoDB()
  db.setDatabase('gdzc')
  res.json({ msg: '数据服务重启成功' })
})

router.post('/set/:database/', (req, res) => {
  db.setDatabase(req.params.database)
  res.json(msg.success)
})

// 新增
router.post('/add/:collection/', (req, res) => {
  req.body._id = ObjectId(req.body._id)
  db.insert(req.params.collection, req.body, doc => {
    res.json(req.body._id)
  })
})
// 更新
router.post('/update/:collection/', (req, res) => {
  // console.log('add->')
  // console.log(req)
  req.body._id = ObjectId(req.body._id) // 必须转ObjectId
  db.updateOne(req.params.collection, { _id: req.body._id }, req.body, doc => {
    res.json(doc)
  })
})

// 查询
// 返回1个结果，默认按时间逆序
// http://localhost:3000/api/get/gonggao/?key=value&key1=value1
router.get('/getone/:collection/', function (req, res, next) {
  let filter = req.query
  filter._id = ObjectId(filter._id)
  db.findOne(req.params.collection, filter, doc => {
    if (!doc) {
      res.json(msg.noFound)
    } else {
      res.json(doc)
    }
  })
})
// 查询
// http://localhost:3000/api/get/gonggao/?key=value&key1=value1
router.get('/get/:collection/', function (req, res, next) {
  let filter = req.query
  // console.log(filter)
  db.find(req.params.collection, filter, { _id: -1 }, doc => {
    if (!doc) {
      res.json(msg.noFound)
    } else {
      res.json(doc)
    }
  })
})

// 查询
// http://localhost:3000/api/getUserList
router.get('/getUserList', function (req, res, next) {
  db.find('users', {}, { _id: -1 }, doc => {
    if (!doc) {
      res.json(msg.noFound)
    } else {
      let n = []
      _.forEach(doc, value => {
        n.push(_.pick(value, ['_id', 'username', 'realName', 'email']))
      })
      res.json(n)
    }
  })
})

// 查询
// http://localhost:3000/api/get/gonggao/?key=value&key1=value1
// {_id: -1} _id ObjectId 倒序排序
router.get('/get/:collection/:skip/:limit', function (req, res, next) {
  let filter = req.query
  // console.log(filter)
  db.find(req.params.collection, filter, { _id: -1 }, parseInt(req.params.skip), parseInt(req.params.limit), doc => {
    if (!doc) {
      res.json(msg.noFound)
    } else {
      res.json(doc)
    }
  })
})

// 查询 去掉content和summary，减少数据量
// http://localhost:3000/api/get/news/?key=value&key1=value1
// {_id: -1} _id ObjectId 倒序排序
router.get('/find/:collection/:skip/:limit', function (req, res, next) {
  let filter = req.query
  // console.log(filter)
  db.find(req.params.collection, filter, { _id: -1 }, parseInt(req.params.skip), parseInt(req.params.limit), {
    content: 0,
    summary: 0,
    img: 0
  }, doc => {
    if (!doc) {
      res.json(msg.noFound)
    } else {
      res.json(doc)
    }
  })
})

// 查询
// http://localhost:3000/api/get/news/?key=value&key1=value1
// {_id: -1} _id ObjectId 倒序排序
router.post('/find/:collection/:skip/:limit', function (req, res, next) {
  let filter = req.query
  let project = req.body
  // console.log(filter)
  db.find(req.params.collection, filter, { _id: -1 }, parseInt(req.params.skip), parseInt(req.params.limit), project, doc => {
    if (!doc) {
      res.json(msg.noFound)
    } else {
      res.json(doc)
    }
  })
})

// 取头条，数量:limit
router.get('/top/:collection/:limit', function (req, res, next) {
  let filter = { column: '头条' }
  db.find(req.params.collection, filter, { _id: -1 }, 0, parseInt(req.params.limit), { content: 0 }, doc => {
    if (!doc) {
      res.json(msg.noFound)
    } else {
      res.json(doc)
    }
  })
})

// 查询数量
router.get('/count/:collection/', function (req, res, next) {
  let filter = req.query
  db.count(req.params.collection, filter, n => {
    // console.log(n)
    res.json({ number: n })
  })
})

// 查询
// http://localhost:3000/api/get/gonggao/key/value
router.get('/get/:collection/:key/:value', function (req, res, next) {
  let filter = {}
  let key = req.params.key
  filter[key] = req.params.value
  // console.log(filter)
  db.findOne(req.params.collection, filter, doc => {
    if (!doc) {
      res.json(msg.noFound)
    } else {
      res.json(doc)
    }
  })
})

// 按id查询
router.get('/get/:collection/:id', function (req, res, next) {
  db.findOne(req.params.collection, { _id: ObjectId(req.params.id) }, doc => {
    if (!doc) {
      res.json(msg.noFound)
    } else {
      res.json(doc)
    }
  })
})

// 删除
router.delete('/delete/:collection/:id', function (req, res) {
  db.deleteOne(req.params.collection, { _id: ObjectId(req.params.id) }, doc => {
    if (!doc) {
      res.json(msg.noFound)
    } else {
      res.json(doc)
    }
  })
})
export default router
